﻿#region MIT Licenze
//The MIT License

//Copyright (c) 2008 Sebastian Faltoni, dotnetfireball.org

//Permission is hereby granted, free of charge, to any person obtaining a copy
//of this software and associated documentation files (the "Software"), to deal
//in the Software without restriction, including without limitation the rights
//to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
//copies of the Software, and to permit persons to whom the Software is
//furnished to do so, subject to the following conditions:

//The above copyright notice and this permission notice shall be included in
//all copies or substantial portions of the Software.

//THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
//IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
//FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
//AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
//LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
//OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
//THE SOFTWARE.
#endregion
using System;
using System.Collections.Generic;
using System.Text;
using System.Web.Security;
using VistaDB.Provider;
using System.Configuration;
using System.Collections.Specialized;
using VistaDB.Diagnostic;
using System.Diagnostics;
using System.Configuration.Provider;
using VistaDB;
using System.Web.Hosting;


namespace Fireball.VistaDB
{

    /// <summary>
    /// Manages storage of role membership information for an ASP.NET application in a VistaDB database.
    /// </summary>
    public class VistaDBRoleProvider : RoleProvider
    {
        private string _connectionString;
        private string _ApplicationName;
        private ConnectionStringSettings _ConnectionStringSettings;

        /// <summary>
        /// Adds the specified user names to each of the specified roles.
        /// </summary>
        /// <param name="usernames">A string array of user names to be added to the specified roles.</param>
        /// <param name="rolenames">A string array of role names to add the specified user names to.</param>
        public override void AddUsersToRoles(string[] usernames, string[] rolenames)
        {
            foreach (string rolename in rolenames)
            {
                if (!this.RoleExists(rolename))
                {
                    throw new ProviderException("Role name not found.");
                }
            }
            foreach (string username in usernames)
            {
                if (username.Contains(","))
                {
                    throw new ArgumentException("User names cannot contain commas.");
                }
                foreach (string rolename in rolenames)
                {
                    if (this.IsUserInRole(username, rolename))
                    {
                        throw new ProviderException("User is already in role.");
                    }
                }
            }
            using (VistaDBConnection conn = new VistaDBConnection(_connectionString))
            {
                conn.Open();

                using (var trans = conn.BeginTransaction())
                {
                    try
                    {
                        using (VistaDBCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "INSERT INTO aspnet_UsersInRoles (RoleId,UserId) VALUES (@RoleId,@UserId)";

                            foreach (string u in usernames)
                                foreach (string r in rolenames)
                                {
                                    var roleId = GetRoleId(r, conn);
                                    var userId = GetUserId(u, conn);
                                    cmd.Parameters.Clear();
                                    cmd.Parameters.AddWithValue("@RoleId", roleId);
                                    cmd.Parameters.AddWithValue("@UserId", userId);
                                    cmd.ExecuteNonQuery();
                                }
                        }

                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw ex;
                    }
                    finally
                    {
                        trans.Dispose();
                    }
                }
            }
        }

        private Guid GetUserId(string username, VistaDBConnection conn)
        {
            using (VistaDBCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "SELECT UserId FROM aspnet_Users WHERE LoweredUserName=@Name";
                cmd.Parameters.AddWithValue("@Name", username.ToLower());

                var o = cmd.ExecuteScalar();

                if (o == null)
                    return Guid.Empty;

                return (Guid)o;
            }
        }

        private Guid GetRoleId(string rolename, VistaDBConnection conn)
        {
            using (VistaDBCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "SELECT RoleId FROM aspnet_Roles WHERE LoweredRoleName=@Name";
                cmd.Parameters.AddWithValue("@Name", rolename.ToLower());

                var o = cmd.ExecuteScalar();

                if (o == null)
                    return Guid.Empty;

                return (Guid)o;
            }
        }

        /// <summary>
        /// Adds a new role to the role database.
        /// </summary>
        /// <param name="rolename">The name of the role to create.</param>
        public override void CreateRole(string rolename)
        {
            if (rolename.Contains(","))
            {
                throw new ArgumentException("role names cannot contain commas.");
            }
            if (this.RoleExists(rolename))
            {
                throw new ProviderException("role name already exists.");
            }
            using (VistaDBConnection conn = new VistaDBConnection(this._connectionString))
            {
                conn.Open();
                using (VistaDBCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "insert into aspnet_roles  (rolename, roleid ,loweredrolename,applicationid)  values(@rolename,@roleid,@loweredrolename, @applicationid)";

                    cmd.Parameters.Add("@rolename", VistaDBType.VarChar, 0xff).Value = rolename;
                    cmd.Parameters.Add("@roleid", VistaDBType.UniqueIdentifier).Value = Guid.NewGuid();
                    cmd.Parameters.Add("@loweredrolename", VistaDBType.VarChar, 0xff).Value = rolename.ToLower();
                    cmd.Parameters.Add("@applicationid", VistaDBType.UniqueIdentifier).Value = this.GetApplicationID();

                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Removes a role from the role database.
        /// </summary>
        /// <param name="rolename">The name of the role to delete.</param>
        /// <param name="throwonpopulatedrole">If true, throws an exception if roleName has one or more members.</param>
        /// <returns>true if the role was successfully deleted; otherwise, false.</returns>
        public override bool DeleteRole(string rolename, bool throwonpopulatedrole)
        {
            if (!this.RoleExists(rolename))
            {
                throw new ProviderException("role does not exist.");
            }
            if (((throwonpopulatedrole && (this.GetUsersInRole(rolename).Length > 0)) ? 1 : 0) != 0)
            {
                throw new ProviderException("cannot delete a populated role.");
            }

            using (VistaDBConnection conn = new VistaDBConnection(this._connectionString))
            {
                conn.Open();
                Guid roleId = GetRoleId(rolename, conn);

                using (VistaDBCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "DELETE FROM aspnet_UsersInRoles WHERE RoleId=@RoleId";

                    cmd.Parameters.Add("@RoleId", VistaDBType.UniqueIdentifier).Value = roleId;

                    cmd.ExecuteNonQuery();
                }

                using (VistaDBCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "DELETE FROM aspnet_Roles WHERE ApplicationId=@ApplicationId and RoleId=@RoleId";

                    cmd.Parameters.Add("@ApplicationId", VistaDBType.UniqueIdentifier).Value = GetApplicationID();
                    cmd.Parameters.Add("@RoleId", VistaDBType.UniqueIdentifier).Value = roleId;

                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }

            return true;
        }


        /// <summary>
        /// Gets an array of user names in a role where the user name contains the specified user name to match.
        /// </summary>
        /// <param name="rolename">The role to search in.</param>
        /// <param name="usernametomatch">The user name to search for.</param>
        /// <returns>A string array containing the names of all the users where the user name matches usernameToMatch and the user is a member of the specified role.</returns>
        public override string[] FindUsersInRole(string rolename, string usernametomatch)
        {
            string[] roles = new string[0];

            using (VistaDBConnection conn = new VistaDBConnection(this._connectionString))
            {
                conn.Open();
                using (VistaDBCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select username from aspnet_usersinroles  where username like @usernamesearch and rolename = @rolename and applicationid = @applicationid";
                    cmd.Parameters.Add("@usernamesearch", VistaDBType.VarChar, 0xff).Value = usernametomatch;
                    cmd.Parameters.Add("@rolename", VistaDBType.VarChar, 0xff).Value = rolename;
                    cmd.Parameters.Add("@applicationid", VistaDBType.UniqueIdentifier).Value = this.GetApplicationID();
                    StringCollection sColl = new StringCollection();

                    using (VistaDBDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            sColl.Add(reader.GetString(0));
                        }

                        if (sColl.Count > 0)
                        {
                            roles = new string[sColl.Count];
                            sColl.CopyTo(roles, 0);                            
                        }
                    }
                }
                conn.Close();
            } 
            return roles;
        }

        /// <summary>
        /// Gets a list of all the roles for the application.
        /// </summary>
        /// <returns>A string array containing the names of all the roles stored in the database for a particular application.</returns>
        public override string[] GetAllRoles()
        {
            using (VistaDBConnection conn = new VistaDBConnection(this._connectionString))
            {
                conn.Open();
                using (VistaDBCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select rolename from aspnet_roles  where applicationid = @applicationid";
                    cmd.Parameters.Add("@applicationid", VistaDBType.UniqueIdentifier).Value = this.GetApplicationID();

                    using (VistaDBDataReader reader = cmd.ExecuteReader())
                    {
                        StringCollection sColl = new StringCollection();
                        while (reader.Read())
                        {
                            sColl.Add(reader.GetString(0));
                        }
                        if (sColl.Count > 0)
                        {
                            string[] roles = new string[sColl.Count];
                            sColl.CopyTo(roles, 0);
                            return roles;
                        }
                        return new string[0];
                    }
                }
            }
        }

        private Guid _CacheAppId = Guid.Empty;
        private Guid GetApplicationID()
        {
            if (_CacheAppId != Guid.Empty)
                return _CacheAppId;
            Guid appId = Guid.Empty;
            using (VistaDBConnection conn = new VistaDBConnection(this._connectionString))
            {
                conn.Open();
                using (VistaDBCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT ApplicationId FROM aspnet_Applications WHERE LoweredApplicationName=@AppName";
                    cmd.Parameters.AddWithValue("@AppName", this.ApplicationName.ToLower());                    
                    object o = cmd.ExecuteScalar();
                    if (o != null)
                    {
                        appId = (Guid)o;
                    } 
                }
                if (appId == Guid.Empty)
                {
                    using (VistaDBCommand cmd = conn.CreateCommand())
                    {
                        appId = Guid.NewGuid();
                        cmd.CommandText = "INSERT INTO aspnet_Applications (ApplicationName,ApplicationId,LoweredApplicationName,Description) VALUES(@ApplicationName,@ApplicationId,@LoweredApplicationName,@Description)";
                        cmd.Parameters.AddWithValue("@ApplicationName", this.ApplicationName);
                        cmd.Parameters.AddWithValue("@ApplicationId", appId);
                        cmd.Parameters.AddWithValue("@LoweredApplicationName", this.ApplicationName.ToLower());
                        cmd.Parameters.AddWithValue("@Description", this.Description);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            _CacheAppId = appId;
            return appId;
        }

        /// <summary>
        /// Gets a list of the roles that a user is in.
        /// </summary>
        /// <param name="username">The user to return a list of roles for. </param>
        /// <returns>A string array containing the names of all the roles that the specified user is in.</returns>
        public override string[] GetRolesForUser(string username)
        {
            using (VistaDBConnection conn = new VistaDBConnection(this._connectionString))
            {
                conn.Open();

                var userId = GetUserId(username, conn);

                if (userId == Guid.Empty)
                    throw new Exception("Role specified don't exist");

                using (VistaDBCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT * FROM aspnet_UsersInRoles as grp 
                            inner join aspnet_Roles as usr on grp.RoleId = usr.RoleId WHERE 
                            grp.UserId=@UserId And usr.ApplicationId=@ApplicationId";
                    cmd.Parameters.AddWithValue("@ApplicationId", this.GetApplicationID());
                    cmd.Parameters.AddWithValue("@UserId", userId);

                    using (VistaDBDataReader r = cmd.ExecuteReader())
                    {

                        List<string> roles = new List<string>();

                        while (r.Read())
                        {
                            roles.Add(r["RoleName"].ToString());
                        }
                        r.Close();

                        return roles.ToArray();
                    }
                }
            } 
        }


        /// <summary>
        /// Gets a list of users in the specified role.
        /// </summary>
        /// <param name="rolename">The name of the role to get the list of users for. </param>
        /// <returns>A string array containing the names of all the users who are members of the specified role.</returns>
        public override string[] GetUsersInRole(string rolename)
        {
            
            using (VistaDBConnection conn = new VistaDBConnection(this._connectionString))
            {
                conn.Open();

                var roleId = GetRoleId(rolename,conn);

                if(roleId == Guid.Empty)
                    throw new Exception("Role specified don't exist");

                using (VistaDBCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT * FROM aspnet_UsersInRoles as grp 
                            inner join aspnet_Users as usr on grp.UserId = usr.UserId WHERE 
                            grp.RoleId=@RoleID And usr.ApplicationId=@ApplicationId";
                    cmd.Parameters.AddWithValue("@ApplicationId", this.GetApplicationID());
                    cmd.Parameters.AddWithValue("@RoleID", roleId);

                    using (VistaDBDataReader r = cmd.ExecuteReader())
                    {
                        
                        List<string> users = new List<string>();

                        while (r.Read())
                        {
                            users.Add(r["UserName"].ToString());
                        }
                        r.Close();

                        return users.ToArray();
                    }
                }
            }
        }



        /// <summary>
        /// Initializes the VistaDB role provider with the property values specified in the ASP.NET application's configuration file. This method is not intended to be used directly from your code.
        /// </summary>
        /// <param name="name">The name of the VistaDBRoleProvider instance to initialize. </param>
        /// <param name="config">A System.Collections.Specialized..::.NameValueCollection that contains the names and values of configuration options for the role provider.</param>
        public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
            {
                throw new ArgumentNullException("config");
            }
            if ((((name == null) || (name.Length == 0)) ? 1 : 0) != 0)
            {
                name = "OdbcRoleProvider";
            }
            if (string.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "VistaDB Role provider");
            }
            base.Initialize(name, config);
            if ((((config["applicationName"] == null) || (config["applicationName"].Trim() == "")) ? 1 : 0) != 0)
            {
                this._ApplicationName = HostingEnvironment.ApplicationVirtualPath;
            }
            else
            {
                this._ApplicationName = config["applicationName"];
            }
            this._ConnectionStringSettings = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];
            if ((((this._ConnectionStringSettings == null) || (this._ConnectionStringSettings.ConnectionString.Trim() == "")) ? 1 : 0) != 0)
            {
                throw new ProviderException("Connection string cannot be blank.");
            }
            this._connectionString = this._ConnectionStringSettings.ConnectionString;
        }

        /// <summary>
        /// Gets a value indicating whether the specified user is in the specified role.
        /// </summary>
        /// <param name="username">The user name to search for.</param>
        /// <param name="rolename">The role to search in.</param>
        /// <returns>true if the specified user name is in the specified role; otherwise, false.</returns>
        public override bool IsUserInRole(string username, string rolename)
        {
            using (VistaDBConnection conn = new VistaDBConnection(this._connectionString))
            {
                conn.Open();

                var userId = GetUserId(username, conn);
                var roleId = GetRoleId(rolename, conn);
                
                using (VistaDBCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT COUNT(*) FROM aspnet_UsersInRoles WHERE RoleId=@RoleId And UserId=@UserId";
                    cmd.Parameters.AddWithValue("@UserId", userId);
                    cmd.Parameters.AddWithValue("@RoleId", roleId);

                    if (cmd.ExecuteNonQuery() > 0)
                        return true;
                    else
                        return false;
                }
            }
        }

        /// <summary>
        /// Removes the specified user names from the specified roles.
        /// </summary>
        /// <param name="usernames">A string array of user names to be removed from the specified roles.</param>
        /// <param name="rolenames">A string array of role names to remove the specified user names from.</param>
        public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames)
        {
            foreach (string rolename in rolenames)
            {
                if (!this.RoleExists(rolename))
                {
                    throw new ProviderException("role name not found.");
                }
            }
            using (VistaDBConnection conn = new VistaDBConnection(_connectionString))
            {
                conn.Open();

                using (var trans = conn.BeginTransaction())
                {
                    try
                    {
                        using (VistaDBCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "DELETE FROM aspnet_UsersInRoles WHERE RoleId=@RoleId And UserId=@UserId";

                            foreach (string u in usernames)
                                foreach (string r in rolenames)
                                {
                                    var roleId = GetRoleId(r, conn);
                                    var userId = GetUserId(u, conn);

                                    cmd.Parameters.Clear();
                                    cmd.Parameters.AddWithValue("@RoleId", roleId);
                                    cmd.Parameters.AddWithValue("@UserId", userId);
                                    cmd.ExecuteNonQuery();
                                }
                        }

                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw ex;
                    }
                    finally
                    {
                        trans.Dispose();
                    }
                }
            }
        }

        /// <summary>
        /// Gets a value indicating whether the specified role name already exists in the role database.
        /// </summary>
        /// <param name="rolename">The name of the role to search for in the database. </param>
        /// <returns>true if the role name already exists in the database; otherwise, false.</returns>
        public override bool RoleExists(string rolename)
        {
            using (VistaDBConnection conn = new VistaDBConnection(this._connectionString))
            {
                conn.Open();
                var roleId = GetRoleId(rolename, conn);

                if (roleId == Guid.Empty)
                    return false;
                else
                    return true;
            }
        }

        /// <summary>
        /// Gets or sets the name of the application for which to store and retrieve role information. 
        /// </summary>
        public override string ApplicationName
        {
            get
            {
                return this._ApplicationName;
            }
            set
            {
                this._ApplicationName = value;
                this._CacheAppId = Guid.Empty;
            }
        }
    }
}
